<?php
namespace app\api\controller\job;
use app\common\annotation\ApiNodeAnnotation;
use app\common\controller\ApiBase;
use think\Db;

/**
 * @ApiNodeAnnotation(
 *     title="新西南教育职位专栏模块",
 *      parent="api/job.index",
 *     is_release=0,
 *     auth=1,
 * )
 */
class Column extends ApiBase
{
     /**
     * @ApiNodeAnnotation(
     *     title="考试列表",
     *     parent="api/job.column",
     *     is_release=1,
     *     auth=1,
     * )
     */
    public function columnlist($page=1,$limit=15)
    {
        //职位专栏数据
        $coumnList = Db::connect("xxn_config")->table("cp_column")
            ->page($page)
            ->limit($limit)
            ->field("id,name,is_reco")
            ->where("is_status",2)
            ->select();

        $this->success("职位表数据专栏",'',$coumnList);
    }

    /**
     * @ApiNodeAnnotation(
     *     title="职位数据专栏",
     *     parent="api/job.column",
     *     is_release=1,
     *     auth=1,
     * )
     */
    public function columnJob($columnid='',$page=1,$limit=15)
    {
        //条件组合
        $where = [];
        $major = '';
        //搜索参数获取
        $param = $this->request->param();
        //根据地区
        if(isset($param['area']) && !empty($param['area'])) array_push($where,['area','like',"%{$param['area']}%"]);
        //根据学历
        if(isset($param['schooling']) && !empty($param['schooling'])) array_push($where,['schooling','like',"%{$param['schooling']}%"]);
        //根据专业
        if(isset($param['major']) && !empty($param['major']))
        {
            array_push($where,['major','like',"%{$param['major']}%"]);
        }else{
            $major = "schooling_college is null and schooling_undergraduate is null and schooling_graduate is null";
        }
        //根据政治面貌
        if(isset($param['face']) && !empty($param['face'])) array_push($where,['face','like',"%{$param['face']}%"]);
        //专栏数据
        if($columnid) array_push($where,['a.column_id','eq',$columnid]);
        //根据id 集合获取数据
        if(isset($param['jobid'])&& !empty($param['jobid'])) array_push($where,['a.id','in',explode(',',$param['jobid'])]);


        $coumnList = Db::connect("xxn_config")->table("cp_jobtable")
            ->alias("a")
            ->where($major)
            /*->leftJoin("cp_column b",'a.column_id=b.id')
            ->where("b.is_status",'eq',2)
            ->where("b.is_position",'eq',2)*/
            ->page($page)
            //->where("a.column_id",$columnid)
            ->where($where)
            ->order("a.department")
            ->field('a.id,department,department_code,department_telphone,department_website,job_name,job_type,job_code,job_bk_type,jobs,job_desc,job_intro,job_needs,area,category,schooling,schooling_college,schooling_undergraduate,schooling_graduate,schooling_other,job_other_desc,year,address,face,work_experience,degree,property,unit,note,polytechnic_school,four_project,major,column_id,out_student,minorities,level_code,five_project')
            ->paginate($limit);

        $this->success("职位表专栏数据表职位",'',$coumnList);
    }

    /**
     * @ApiNodeAnnotation(
     *     title="专栏详情",
     *     parent="api/job.column",
     *     is_release=1,
     *     auth=1,
     * )
     */
    public function columndetails($columnid)
    {
        $coumnList = Db::connect("xxn_config")->table("cp_column")
            ->where('id',$columnid)
            ->field('id,name,thumbnail')
            ->find();
        $this->success('专栏详情','',$coumnList);
    }

    /**
     * @ApiNodeAnnotation(
     *     title="职位详情",
     *     parent="api/job.column",
     *     is_release=1,
     *     auth=1,
     * )
     */
    public function jobdetails($jobid)
    {
        //职位信息
        $job = Db::connect("xxn_config")->table("cp_jobtable")
            ->where('id',$jobid)
            ->find();

        //职位历年竞争分数，取职位，招录人数，年份，缴费人数，计算后竞争比列
        $formerYearsScore = Db::connect("xxn_config")->table('cp_job')->where("a.department", "like", "%{$job['department']}%")
            ->alias('a')
            //->leftJoin("cp_column c", 'a.column_id=c.id')
            //->where("c.is_job", 'eq', 2)
            ->where("a.category", $job['category'])
            ->field("sum(cost_number) cost_number,left(a.year,4) year,department,job_name,a.department_code,a.category,a.id")
            ->group("a.job_name,left(a.year,4)")
            ->order('year desc')
            ->select();

        //计算竞争分数，和职位招录人数
        $newFormerYearsScore = [];
        foreach ($formerYearsScore as $k => $v)
        {
            $str = preg_replace('/[\W]/', '', $v['job_name']);
            $position_count = Db::connect("xxn_config")->table("cp_jobtable")->alias('a')
                ->leftJoin("cp_column b", 'a.column_id=b.id')
                ->where("department", 'like', "%{$job['department']}%")
                ->where("b.is_position", 'eq', 2)
                ->where("a.year", "like", "%{$v['year']}%")
                ->where("a.job_code", "like", "%{$str}%")
                ->where("a.category", $v['category'])
                ->field("sum(job_needs) job_needs,job_name,job_code")
                ->find();
            if ($position_count['job_needs'])
            {
                //缴费人数除以招录人=竞争比列
                $formerYearsScore[$k]['competition'] = round($v['cost_number'] / $position_count['job_needs'], 0);
            } else {
                $formerYearsScore[$k]['competition'] = 0;
            }
            $formerYearsScore[$k]['job_needs'] = $position_count['job_needs'];
            if ($formerYearsScore[$k]['job_needs'])
            {
                $newFormerYearsScore[] = $formerYearsScore[$k];
                //表格合并数据
               /* $newFormerYearsScore[$v['year']]['value'][] = $competition[$k];
                $newFormerYearsScore[$v['year']]['rowspan'] = count($newFormerYearsScore[$v['year']]['value']);
                $newFormerYearsScore[$v['year']]['year'] = $v['year'];*/
            }
        }

        //历年职位进面比列 取最低分，和平均分，以及年份
        $formerYearsCompetition = Db::connect("xxn_config")
            ->table('cp_positionscore')->where("DepartmentName", "like", "%{$job['department']}%")
            ->field("round(avg(WriteScore),2) WriteResultScoreAvg,min(WriteScore) as score,PositionName as job_name,left(year,4) year,a.category,a.PositionNumber")
            ->group("a.PositionNumber,left(a.year,4)")
            ->limit(5)
            ->alias('a')
            ->leftJoin("cp_column c", 'a.column_id=c.id')
            ->where("c.is_interview", 'eq', 2)
            ->where("a.category", 'like',"%{$job['category']}%")
            ->select();

        //计算历年职位进面比列
        $newFormerYearsCompetition = [];
        foreach ($formerYearsCompetition as $key => $value)
        {
            //单位招录职位
            $value['job_number'] =  Db::connect("xxn_config")->table("cp_jobtable")->where("department", 'like', "%{$job['department']}%")
                ->alias('a')
                ->leftJoin("column b", 'a.column_id=b.id')
                ->where("b.is_position", 'eq', 2)
                ->where("a.category", isset($value['category'])?$value['category']:'')
                ->field("sum(job_needs) job_needs,job_name,job_code,count(job_name) job_number")
                ->where("a.year", "like", "%{$value['year']}%")
                ->where("a.job_code", "like", "%{$value['PositionNumber']}%")
                ->count();
            $newFormerYearsCompetition[] = $value;
            //表格合并数据
            /*if ($newFormerYearsCompetition[$value['year']]['job_number'])
            {
                $newFormerYearsCompetition[$value['year']]['value'][] = $value;
                $newFormerYearsCompetition[$value['year']]['rowspan'] = count($newFormerYearsCompetition[$value['year']]['value']);
                $newFormerYearsCompetition[$value['year']]['year'] = $value['year'];
            } else {
                unset($newFormerYearsCompetition[$value['year']]);
            }*/
        }

        $data = [
            //职位历年进面分数线
            "formerYearsScore"       => $newFormerYearsCompetition ,
            //职位历年竞争比列
            "formerYearsCompetition" => $newFormerYearsScore,
            //职位信息
            "jobInfo"                => $job
        ];

        $this->success('职位详情','',$data);
    }

}